Populate the Staging tables
*Create a new SSIS project.
- Open Microsoft Visual Studio ==> Right click on File ==> Select New ==> Click on Project.
- The New Project dialog box will appear, Then Click on Business Intelligence ==> Right on click Integration Service ==> Select Integration Service Project ==> Name a new project ==> Click Ok.

*Download the C# Script attached at the beginning
- Create a new SSIS package inside a newly created SSIS project.
- Darg and drop SSIS Data Flow Task inside control flow ==> Double click on Data Flow Task to go inside.
- Darg and drop SSIS Script Component inside Data Flow Task from SSIS toolbox ==> Double click Script Componet ==> Click on Inputs and Outputs ==> Click on plus(+) next output ==> Click on plus(+) next to output Columns ==> Set up all neccessary Output Columns.
- Click on Script ==> Click on Edit Script this will opens up Microsoft Visual Studio ==> Copy C# Script code that had been attached at the beginning ==> past it here inside Microsoft Visual Studio.
- Click on Save ==> CloseMicrosoft Visual Studio ==> Click ok on Script Transformation Editor dialog box.

- Drag Execute SQL Task ==> Double Click and Setup Connection manager ==> copy and paste the following SQL Query code. This will Truncate the staging table every time the SSIS package runs.
TRUNCATE TABLE US_Mass_shooting_1966_2017_Stg
- Connect the Precedence Constraint from Execute SQL Task to Data Flow Task.

*All necessary data clean-ups, data validation, and data conversion need to be done before data loaded from source JSON files to the destination SQL Server staging database.
- Drag and drop the Data Conversion transformation ==> to convert the appropriate Data Types.
- Drag and drop Derived Column Transformation ==> Copy and paste the following code inside the Derived Column transformation Editor Expression field. Class == "MS" ? "Mass Shooting" : Class == "FMS" ? "Family Murder Suicide" : Class == "SPK" ? "Spree Killing" : Class == "SEK" ? "Serial Killing" : Class == "GD" ? "Gang or Drug Related" : Class

- Drag and drop the OLEDB Destination ==> Configure the destination connection manager ==> select the staging table called "US_Mass_Shooting_1966_2017_Stg" from the Name of the table or the view drop-down list.
- Mapp source columns to destination columns.
- Click on Start to run the package.

*To make sure all necessary data loaded from source to destination, Retrieve all records from Destination SQL server staging database tables.
*Copy the following SQL Script and past it inside Microsoft SQL Server Management Studio ==> Click Execute. This will retrieve records from two staging tables.
USE [CAP_Project_One_Staging]
GO
SELECT * FROM [dbo].[US_Mass_shooting_1966_2017_Stg]
SELECT * FROM [dbo].[US_Mass_Shooting_1966_2019]
![]()